package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;

import com.feib.soeasy.dao.Soez5001Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.service.Soez5001ExcelService;
import com.feib.soeasy.util.CodeTableUtil;
import com.feib.soeasy.util.UserUtil;

/**
 * @title (#)soez5001ExcelServiceImpl.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/12 create by Jimmy Liu<br>
 */
@Service("soez5001ExcelService")
public class Soez5001ExcelServiceImpl extends BaseServiceImpl implements Soez5001ExcelService {

    @Resource(name = "dataSource")
    private DataSource dataSource;

    @Resource(name = "soez5001Dao")
    private Soez5001Dao soez5001Dao;
    /**
     * 暫存目錄
     */
    @Resource(name = "tempDirectory")
    private String tempDirectory;

    private static final String[] titles = {
            "客戶編號", "繳款帳號 ", "客戶名稱", "異常原因", "繳款通路", "繳款截止日", "繳款交易日", "繳款核銷日"
            , "應繳金額 ", "實繳金額", "應退補差額" };

    private static final String[] titlesSchool = {
            "學號", "繳款帳號 ", "客戶名稱", "異常原因", "繳款通路", "繳款截止日", "繳款交易日", "繳款核銷日"
            , "應繳金額1 ", "實繳金額1", "應退補差額1", "應繳金額2 ", "實繳金額2", "應退補差額2" };

    /**
     * FEEDBACK_LOG
     * 1. USERNO          Varchar (16)                        虛擬帳號
     * 2. ERR_MSG         Varchar (100)                       異常原因
     * 3.TRMTYP          Char (1)                             通路別(0:臨櫃,1:網銀,2:ATM,3:中心轉帳BIO)
     * 4.ADAY            Varchar (8)                          繳款交易日
     * 5.FBTIME          Datetime                             繳款核銷日
     * 6.PAYMENT.TOTAL_AMOUNT            Numeric (19,0)       應繳金額
     * 7. TXAMT           Numeric (11,0)                      實繳金額
     * //學校才需要
     * 8.PAYMENT.TOTAL_LOANABLE_AMOUNT           Numeric (19,0)      應繳金額2(可貸金額)
     * 9. PAYMENT.TOTAL_AMOUNT - PAYMENT.TOTAL_AMOUNT    Numeric (19,0)            應退補差額(可貸)
     */
    public String doExportXls(Group group) throws Exception {

        boolean isSchool =  (null != group && Group.GROUP_TYPE_SCHOOL.equals(group.getGroupType()));
        
        Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

        CreationHelper createHelper = wb.getCreationHelper();

        Map<String, CellStyle> styles = createStyles(wb);

        Sheet sheet = wb.createSheet("人工核銷明細表");
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        // 行數
        int rownum = 0;

        // title row
        // 創建一行並放一些單元格到該行中，行的索引是以0開始的
        Row titleRow = sheet.createRow(rownum++);
        titleRow.setHeightInPoints(33);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("人工核銷明細表");
        titleCell.setCellStyle(styles.get("title"));
        if (isSchool)
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));
        else
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1"));

        // printDate row
        Row printDateRow = sheet.createRow(rownum++);
        printDateRow.setHeightInPoints((float) 22.5);
        Cell printDateCell = printDateRow.createCell(5);
        if (isSchool)
            printDateCell = printDateRow.createCell(9);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
        printDateCell.setCellValue("製表日期/時間：" + dateFormat.format(Calendar.getInstance().getTime()));
        printDateCell.setCellStyle(styles.get("title2"));
        if (isSchool)
            sheet.addMergedRegion(CellRangeAddress.valueOf("$J$2:$N$2"));
        else
            sheet.addMergedRegion(CellRangeAddress.valueOf("$F$2:$J$2"));

        // printDateRow row 帳單業者名稱
        Cell conditionGroupNameCell = printDateRow.createCell(0);
        conditionGroupNameCell.setCellValue("帳單業者：" + group.getGroupName());
        conditionGroupNameCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$E$2"));

        // condition row 帳單業者代號
        Row conditionRow = sheet.createRow(rownum++);
        conditionRow.setHeightInPoints((float) 22.5);
        Cell conditionGroupNoCell = conditionRow.createCell(0);
        conditionGroupNoCell.setCellValue("帳單業者代號 (統編+序號)：" + group.getGroupNo());
        conditionGroupNoCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$3:$E$3"));

        /*
        // condition row 資料期間
        Cell conditionDataPeriodCell = conditionRow.createCell(14);
        conditionDataPeriodCell.setCellValue("資料期間：未定義");
        conditionDataPeriodCell.setCellStyle(styles.get("title2"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$O$3:$Q$3"));

        // condition row 檔案名稱
        Cell conditionFileNameCell = conditionRow.createCell(17);
        conditionFileNameCell.setCellValue("檔案名稱：未定義.xls");
        conditionFileNameCell.setCellStyle(styles.get("title2"));
        */
        
        sheet.addMergedRegion(CellRangeAddress.valueOf("$R$3:$T$3"));
        /*
         * //明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。 Row
         * sumaryRow = sheet.createRow(rownum++);
         * sumaryRow.setHeightInPoints((float) 22.5); Cell sumaryCell =
         * sumaryRow.createCell(0); sumaryCell.setCellValue(
         * "明細小計：已繳款 XX筆，金額 $XXX,XXX,XXX元 / 未繳款 XX筆，金額 $XXX,XXX,XXX元 。");
         * sumaryCell.setCellStyle(styles.get("title2"));
         * sheet.addMergedRegion(CellRangeAddress.valueOf("$A$4:$N$4"));
         */
        // header row
        Row headerRow = sheet.createRow(rownum++);
        headerRow.setHeightInPoints((float) 34.5);
        Cell headerCell;
        
        if (isSchool)
        {
            for (int i = 0; i < titlesSchool.length; i++) {
                headerCell = headerRow.createCell(i);
                headerCell.setCellValue(titlesSchool[i]);
                headerCell.setCellStyle(styles.get("header"));
            }  
        }
        else
        {
            for (int i = 0; i < titles.length; i++) {
                headerCell = headerRow.createCell(i);
                headerCell.setCellValue(titles[i]);
                headerCell.setCellStyle(styles.get("header"));
            } 
        }

        List<Object> sqlArgs = new ArrayList<Object>();
        String sql = soez5001Dao.getQuerySql(group, sqlArgs);
        logger.debug("匯出EXCEL SQL:{}", sql);
        logger.debug("匯出EXCEL 參數:{}", sqlArgs);
        JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
        XlsResultSetExtractor extrator = new XlsResultSetExtractor(sheet, group, rownum, styles, dataSource);
        Object nullObject = jdbcTemplate.query(sql, sqlArgs.toArray(), extrator);

        // adjust the column widths
        int colCount = 0;
        //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題 
        //titles.length == 欄位數
        
        if (isSchool)//帳單業者為學校時欄位
        {
          while (colCount <= titlesSchool.length) {
            sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
            }
        
        }else{
            while (colCount <= titles.length) {
                sheet.autoSizeColumn((short) colCount++,true);
            }
            
        }
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
        logger.debug("[人工核銷明細表]暫存目錄:{}", tempDownloadFilePath);
        
        File directory = new File(tempDownloadFilePath);
        if(!directory.exists())
        {
            FileUtils.forceMkdir(directory);
        }

        String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-人工核銷明細表.xls";

        FileOutputStream out;
        out = new FileOutputStream(fileFullName);
        wb.write(out);
        out.flush();
        out.close();

        return fileFullName;
    }

    

    /**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontName("標楷體");
        titleFont.setFontHeightInPoints((short)24);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font title2Font = wb.createFont();
        title2Font.setFontName("標楷體");
        title2Font.setFontHeightInPoints((short)14);
        style = wb.createCellStyle();
        style.setFont(title2Font);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        styles.put("title2", style);

        Font headerFont = wb.createFont();
        headerFont.setFontName("標楷體");
        headerFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(headerFont);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("header", style);

        Font cellFont = wb.createFont();
        cellFont.setFontName("標楷體");
        cellFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setFont(cellFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        styles.put("cell", style);
        
        style = wb.createCellStyle();
        Font cellNumberFont = wb.createFont();
        cellNumberFont.setFontName("Arial");
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
        cellNumberFont.setFontHeightInPoints((short)14);//設定文字大小     
        style.setFont(cellNumberFont);
        style.setAlignment(CellStyle.ALIGN_RIGHT);     
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
        styles.put("cell_number", style);
        
        style = wb.createCellStyle();
        Font cellDateFont = wb.createFont();
        cellDateFont.setFontName("Arial");
        cellDateFont.setFontHeightInPoints((short)12);
        style.setFont(cellDateFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
      //2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
        style.setWrapText(false);//設定文字是否換行
      //2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題 
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
        styles.put("cell_date", style);

        return styles;
    }

    class XlsResultSetExtractor implements ResultSetExtractor<Object> {

        int rownum = 0;

        Sheet sheet = null;
        
        Group group = null;

        Map<String, CellStyle> styles;

        DataSource dataSource;

        Map<Long, List<Map<String, Object>>> commissionMap = new HashMap<Long, List<Map<String, Object>>>();

        SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");

        //String today = sd.format(Calendar.getInstance().getTime());

        public XlsResultSetExtractor(Sheet sheet, Group group, int starRownum, Map<String, CellStyle> styles, DataSource dataSource) {
            this.sheet = sheet;
            this.rownum = starRownum;
            this.styles = styles;
            this.dataSource = dataSource;
            this.group = group;
        }

        protected Map<String, Object> getCommission(String commissionCode, Long formatDefPk) {
            List<Map<String, Object>> commissionCodes = commissionMap.get(formatDefPk);
            if (null == commissionCodes) {
                // 到資料庫抓取
                JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
                StringBuffer sql = new StringBuffer();
                sql.append("select distinct");
                sql.append(" COMMISSION_CODE_TABLE.COMMISSION_CODE_PK");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_CODE_NAME");
                sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_S");
                sql.append(", COMMISSION_CODE_TABLE.AMOUNT_SCOPE_E");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_TYPE");
                sql.append(", COMMISSION_CODE_TABLE.COMMISSION_AMOUNT");
                sql.append(", COMMISSION_CODE_TABLE.BANK_AMOUNT");
                sql.append(", COMMISSION_CODE_TABLE.PAYER_AMOUNT");
                sql.append(", FORMAT_DEF_COMMISSION.FORMAT_DEF_PK");
                sql.append(" from COMMISSION_CODE_TABLE");
                sql.append(" inner join FORMAT_DEF_COMMISSION ");
                sql.append(" on COMMISSION_CODE_TABLE.COMMISSION_CODE_PK = FORMAT_DEF_COMMISSION.COMMISSION_CODE_PK");
                sql.append(" where FORMAT_DEF_COMMISSION.FORMAT_DEF_PK = ?");
                commissionCodes = jdbcTemplate.queryForList(sql.toString(), new Object[] { formatDefPk });

                commissionMap.put(formatDefPk, commissionCodes);
            }

            for (Map<String, Object> result : commissionCodes) {
                if (commissionCode.equalsIgnoreCase((String) result.get("COMMISSION_CODE")))
                    return result;
            }
            return null;
        }

        /*
         * (non-Javadoc)
         * 
         * @see
         * org.springframework.jdbc.core.ResultSetExtractor#extractData(java
         * .sql.ResultSet)
         */
        @Override
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            /*
             * long kTotalValue = 0; long lTotalValue = 0; long mTotalValue = 0;
             * long nTotalValue = 0; long oTotalValue = 0; long pTotalValue = 0;
             * long qTotalValue = 0; long rTotalValue = 0; long sTotalValue = 0;
             * long tTotalValue = 0;
             */
            
            boolean isSchool =  (null != group && Group.GROUP_TYPE_SCHOOL.equals(group.getGroupType()));
            while (rs.next()) {

                // data rows

                Row row = sheet.createRow(rownum++);
                row.setHeightInPoints((float) 24);
                int j = 0;
                
                // 客戶編號/學號
                String aValue = rs.getString("PAYER_ID");
                if (isSchool)
                    aValue = rs.getString("STUDENT_ID");                
                Cell acell = row.createCell(j++);
                acell.setCellValue(aValue);
                acell.setCellStyle(styles.get("cell"));

                // 繳款帳號
                String bValue = rs.getString("USERNO");
                Cell bcell = row.createCell(j++);
                bcell.setCellValue(bValue);
                bcell.setCellStyle(styles.get("cell"));

                // 客戶名稱
                String cValue = rs.getString("PAYER_NAME");
                Cell ccell = row.createCell(j++);
                ccell.setCellValue(cValue);
                ccell.setCellStyle(styles.get("cell"));

                // 異常原因
                String dValue = rs.getString("ERR_MSG");
                Cell dcell = row.createCell(j++);
                dcell.setCellValue(dValue);
                dcell.setCellStyle(styles.get("cell"));

                // 繳款通路
                String channel = rs.getString("FEEDBACK_CHANNEL");
                if (null != channel)
                {
                    if ("7111111".equalsIgnoreCase(channel))
                        channel = "D";
                    else if ("TFM".equalsIgnoreCase(channel))
                        channel = "E";
                    else if ("HILIFE".equalsIgnoreCase(channel))
                        channel = "F";
                    else if ("OKCVS".equalsIgnoreCase(channel))
                        channel = "G";
                    else if ("POST".equalsIgnoreCase(channel))
                        channel = "H";
                    else if ("ACH".equalsIgnoreCase(channel))/* 2012/05/21 顯示ACH通路資料    */
                        channel = "I";
                }
                String eValue = CodeTableUtil.getInstance().getCodeName(CodeTableUtil.CODE_KIND_CHANNEL, channel);
                Cell ecell = row.createCell(j++);
                ecell.setCellValue(eValue);
                ecell.setCellStyle(styles.get("cell"));
                
                // 繳款截止日
                Date fValue = rs.getDate("PAY_DUE");
                Cell fcell = row.createCell(j++);
                if (null != fValue)
                    fcell.setCellValue(fValue);
                fcell.setCellStyle(styles.get("cell_date"));

                // 繳款交易日
                String gValue = rs.getString("ADAY");
                Cell gcell = row.createCell(j++);
                    try {
                        if (null != gValue && 0 != gValue.length())
                            gcell.setCellValue(sd.parse(gValue));
                    }
                    catch (ParseException e) {
                        logger.error("日期格式錯誤 (yyyyMMdd), 原始字=" + gValue);
                    }
                gcell.setCellStyle(styles.get("cell_date"));

                // 繳款核銷日 FBTIME
                Date hValue = rs.getDate("FBTIME");
                Cell hcell = row.createCell(j++);
                if (null != hValue)
                    hcell.setCellValue(hValue);
                hcell.setCellStyle(styles.get("cell_date"));

                // 應繳金額1
                long iValue = rs.getLong("TOTAL_AMOUNT");
                // 實繳金額1
                long jValue = rs.getLong("TXAMT");

                // 實繳金額2
                long lValue = rs.getLong("TOTAL_LOANABLE_AMOUNT");
                
                if (isSchool)
                {
                    if (lValue == jValue)
                    {
                        //使用第2個金額核銷
                        // 應繳金額1
                        Cell icell = row.createCell(j++);
                        icell.setCellValue(iValue);
                        icell.setCellStyle(styles.get("cell_number"));

                        // 實繳金額1
                        Cell jcell = row.createCell(j++);
                        jcell.setCellValue(0);
                        jcell.setCellStyle(styles.get("cell_number"));

                        // 應退補差額1
                        Cell kcell = row.createCell(j++);
                        kcell.setCellValue(0);
                        kcell.setCellStyle(styles.get("cell_number"));
                        
                        // 應繳金額2
                        Cell lcell = row.createCell(j++);
                        lcell.setCellValue(lValue);
                        lcell.setCellStyle(styles.get("cell_number"));

                        // 實繳金額2
                        Cell mcell = row.createCell(j++);
                        mcell.setCellValue(jValue);
                        mcell.setCellStyle(styles.get("cell_number"));

                        // 應退補差額2
                        long nValue = jValue - lValue;
                        Cell ncell = row.createCell(j++);
                        ncell.setCellValue(nValue);
                        ncell.setCellStyle(styles.get("cell_number"));
                        
                    }
                    else
                    {
                        // 應繳金額1
                        Cell icell = row.createCell(j++);
                        icell.setCellValue(iValue);
                        icell.setCellStyle(styles.get("cell_number"));

                        // 實繳金額1
                        Cell jcell = row.createCell(j++);
                        jcell.setCellValue(jValue);
                        jcell.setCellStyle(styles.get("cell_number"));

                        // 應退補差額1
                        long kValue = jValue - iValue;
                        Cell kcell = row.createCell(j++);
                        kcell.setCellValue(kValue);
                        kcell.setCellStyle(styles.get("cell_number"));
                        
                        // 應繳金額2
                        Cell lcell = row.createCell(j++);
                        lcell.setCellValue(lValue);
                        lcell.setCellStyle(styles.get("cell_number"));

                        // 實繳金額2
                        Cell mcell = row.createCell(j++);
                        mcell.setCellValue(0);
                        mcell.setCellStyle(styles.get("cell_number"));

                        // 應退補差額2
                        Cell ncell = row.createCell(j++);
                        ncell.setCellValue(0);
                        ncell.setCellStyle(styles.get("cell_number"));
                    }
                }
                else
                {
                    // 應繳金額
                    Cell icell = row.createCell(j++);
                    icell.setCellValue(iValue);
                    icell.setCellStyle(styles.get("cell_number"));

                    // 實繳金額
                    Cell jcell = row.createCell(j++);
                    jcell.setCellValue(jValue);
                    jcell.setCellStyle(styles.get("cell_number"));

                    // 應退補差額
                    long kValue = jValue - iValue;
                    Cell kcell = row.createCell(j++);
                    kcell.setCellValue(kValue);
                    kcell.setCellStyle(styles.get("cell_number")); 
                }

                
            }
            return null;
        }

        /**
         * @return the rownum
         */
        public int getRownum() {
            return rownum;
        }
    }

}
